{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fa3cbfcf",
   "metadata": {},
   "source": [
    "# 回顾"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "337c6f9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "deployment=\"gpt4\" #在azure中的deployment name\n",
    "model=\"gpt-4\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "99c60769",
   "metadata": {},
   "outputs": [],
   "source": [
    "def translate(text):\n",
    "    messages = []\n",
    "    messages.append( {\"role\": \"system\", \n",
    "                      \"content\": \"You are a translator. Please, translate the user's request to English.\"})\n",
    "    messages.append( {\"role\": \"user\", \"content\": text})\n",
    "    response = openai.ChatCompletion.create(\n",
    "        engine=deployment, \n",
    "        model=model,\n",
    "        messages=messages,\n",
    "        temperature=0.5,\n",
    "        max_tokens = 100\n",
    "    )\n",
    "    return response[\"choices\"][0][\"message\"][\"content\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e2c83b7f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Encoding 'cl100k_base'>\n",
      "chinese:在未来还没有到来的时候，总要有人把它创造出来，那个人应该是我们。 ; 35 tokens\n",
      "\n",
      "english:Before the future arrives, there always needs to be someone to create it, and that person should be us. ; 22 tokens\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import openai\n",
    "import tiktoken\n",
    "\n",
    "encoding = tiktoken.encoding_for_model(\"gpt-4\")\n",
    "\n",
    "print(encoding)\n",
    "\n",
    "chinese = \"\"\"在未来还没有到来的时候，总要有人把它创造出来，那个人应该是我们。\"\"\"\n",
    "english = translate(chinese)\n",
    "\n",
    "num_of_tokens_in_chinese = len(encoding.encode(chinese))\n",
    "num_of_tokens_in_english = len(encoding.encode(english))\n",
    "\n",
    "print(f\"chinese:{chinese} ; {num_of_tokens_in_chinese} tokens\\n\")\n",
    "print(f\"english:{english} ; {num_of_tokens_in_english} tokens\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9daae292",
   "metadata": {},
   "source": [
    "# 常见应用场景"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f8773d0",
   "metadata": {},
   "source": [
    "## 意图识别"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f0713ebf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "设置提醒\n"
     ]
    }
   ],
   "source": [
    "import openai\n",
    "response = openai.ChatCompletion.create(\n",
    "    engine=deployment, # 如果直接访问OpenAI GPT服务的同学，这里不要使用engine这个参数，要使用model，如： model=“gpt-4”\n",
    "    model=model,\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": \"\"\"\n",
    "          Recognize the intent from the user's input \n",
    "         \"\"\"},\n",
    "        #{\"role\": \"user\", \"content\": \"订明天早5点北京到上海的飞机\"}\n",
    "        {\"role\": \"user\", \"content\": \"提醒我明早8点有会议\"}\n",
    "    ]\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "786389c2",
   "metadata": {},
   "source": [
    "## 生成SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ce8e1807",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "首先，我们需要找到英语课程的id，然后在学生表中找到所有参加这门课程的学生的成绩，然后计算平均值。以下是SQL查询：\n",
      "\n",
      "```sql\n",
      "SELECT AVG(score) \n",
      "FROM students \n",
      "WHERE course_id = (\n",
      "    SELECT id \n",
      "    FROM courses \n",
      "    WHERE name = '英语'\n",
      ")\n",
      "```\n",
      "\n",
      "这个查询首先在courses表中找到名为'英语'的课程的id，然后在students表中找到所有参加这门课程的学生的成绩，然后计算这些成绩的平均值。\n"
     ]
    }
   ],
   "source": [
    "import openai, os\n",
    "from langchain.llms import OpenAI\n",
    "\n",
    "system_prompt =  \"\"\"  You are a software engineer, you can anwser the user request based on the given tables:\n",
    "                  table “students“ with the columns [id, name, course_id, score] \n",
    "                  table \"courses\" with the columns [id, name] \n",
    "                  \"\"\"\n",
    "\n",
    "prompt = system_prompt\n",
    "\n",
    "response = openai.ChatCompletion.create(\n",
    "    engine=deployment, # # 如果直接访问OpenAI GPT服务的同学，这里不要使用engine这个参数，要使用model，如： model=“gpt-4”\n",
    "    model=model,\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": prompt},\n",
    "        {\"role\": \"user\", \"content\": \"计算所有学生英语课程的平均成绩\"},\n",
    "    ],\n",
    "    max_tokens = 500\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c6ea8d7",
   "metadata": {},
   "source": [
    "## 生成代码友好的提示词"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03ff7a80",
   "metadata": {},
   "source": [
    "### 规范输出的格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a327cd24",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"intention\": \"set_reminder\", \"parameters\": {\"time\": \"tomorrow 8am\", \"event\": \"meeting\"}}\n"
     ]
    }
   ],
   "source": [
    "import openai\n",
    "response = openai.ChatCompletion.create(\n",
    "    engine=deployment, # engine = \"deployment_name\".\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": \"\"\"\n",
    "          Recognize the intent from the user's input and format output as JSON string. \n",
    "        The output JSON string includes: \"intention\", \"paramters\" \"\"\"},\n",
    "        {\"role\": \"user\", \"content\": \"提醒我明早8点有会议\"}\n",
    "    ]\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e9ddc0b4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT students.name \n",
      "FROM students \n",
      "JOIN courses ON students.course_id = courses.id \n",
      "WHERE courses.name = '英语' AND students.score > 80;\n"
     ]
    }
   ],
   "source": [
    "import openai, os\n",
    "from langchain.llms import OpenAI\n",
    "\n",
    "system_prompt =  \"\"\"  You are a software engineer, you can write a SQL string as the anwser according to the user request \n",
    "               The user's requirement is based on the given tables:\n",
    "                  table “students“ with the columns [id, name, course_id, score];\n",
    "                  table \"courses\" with the columns [id, name].\"\"\"\n",
    "\n",
    "prompt = system_prompt\n",
    "\n",
    "response = openai.ChatCompletion.create(\n",
    "    engine=deployment,\n",
    "    model=model,\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": prompt},\n",
    "        #{\"role\": \"user\", \"content\": \"列出英语成绩大于80分的学生\"},\n",
    "        {\"role\": \"user\", \"content\": \"列出英语课程成绩大于80分的学生, 返回结果只包括SQL\"},\n",
    "        #{\"role\": \"user\", \"content\": \"列出年龄大于13的学生\"}\n",
    "    ],\n",
    "    max_tokens = 500\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "446523a7",
   "metadata": {},
   "source": [
    "### 文本规范异常输出的格式 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "17cfa279",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-1\n"
     ]
    }
   ],
   "source": [
    "import openai, os\n",
    "from langchain.llms import OpenAI\n",
    "\n",
    "system_prompt =  \"\"\"  You are a software engineer, you can write a SQL string as the anwser according to the user request \n",
    "               The user's requirement is based on the given tables:\n",
    "                  table “students“ with the columns [id, name, course_id, score];\n",
    "                  table \"courses\" with the columns [id, name].\"\"\"\n",
    "\n",
    "system_prompt_with_negative =  \"\"\"  \n",
    "You are a software engineer, you can write a SQL string as the anwser according to the user request.\n",
    "Also, when you cannot create the SQL query for the user's request based on the given tables, please, only return \"invalid request\"\n",
    "               The user's requirement is based on the given tables:\n",
    "                  table “students“ with the columns [id, name, course_id, score];\n",
    "                  table \"courses\" with the columns [id, name].\"\"\"\n",
    "\n",
    "#prompt = system_prompt\n",
    "prompt = system_prompt_with_negative\n",
    "\n",
    "response = openai.ChatCompletion.create(\n",
    "    engine=deployment,\n",
    "    model=model,\n",
    "    temperature = 0,\n",
    "    messages=[\n",
    "        {\"role\": \"system\", \"content\": prompt},\n",
    "        {\"role\": \"user\", \"content\": \"列出年龄大于13的学生\"}\n",
    "    ],\n",
    "    max_tokens = 500\n",
    "  )\n",
    "print(response.choices[0].message.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2bac7b0c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31f3122c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
